package com.xyzwps.ewa.modules.user

import com.xyzwps.ewa.common.Gender
import com.xyzwps.ewa.data.database
import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.SqlExpressionBuilder.eq
import org.jetbrains.exposed.sql.transactions.transaction

object UserRepository {
    object UserTable : Table(name = "users") {
        val id = long("id").autoIncrement()
        val name = varchar("name", length = 50)
        val gender = enumeration<Gender>("gender")
        val state = enumeration<UserState>("state")

        override val primaryKey = PrimaryKey(id)

        init {
            uniqueIndex(customIndexName = "uk_username", columns = arrayOf(name))
        }
    }

    init {
        transaction {
            SchemaUtils.create(UserTable)
        }
    }

    /**
     * Create a new user.
     *
     * @param user The user information to create. User id would be ignored.
     */
    fun create(user: User): UserId {
        val rawId = UserTable.insert {
            it[name] = user.name
            it[gender] = user.gender
            it[state] = user.state
        }[UserTable.id]
        return UserId(rawId)
    }

    fun read(id: UserId): User? = UserTable.selectAll()
        .where { UserTable.id eq id.value }
        .map(this::toUser)
        .singleOrNull()

    fun findByPage(pageNo: Int, pageSize: Int): List<User> = UserTable.selectAll()
        .orderBy(UserTable.id to SortOrder.DESC)
        .limit(pageSize, (pageNo - 1) * pageSize * 1L)
        .map(this::toUser)

    fun count(): Int = UserTable.selectAll().count().toInt()

    fun findByUsername(username: String): User? = UserTable.selectAll()
        .where { UserTable.name eq username }
        .map(this::toUser)
        .singleOrNull()

    private fun toUser(row: ResultRow) = User(
        UserId(row[UserTable.id]),
        row[UserTable.name],
        row[UserTable.gender],
        row[UserTable.state]
    )

    fun update(user: User) {
        UserTable.update({ UserTable.id eq user.id.value }) {
            it[name] = user.name
            it[gender] = user.gender
            it[state] = user.state
        }
    }

    fun delete(id: UserId) {
        UserTable.deleteWhere {
            UserTable.id.eq(
                id.value
            )
        }
    }

}